This tutorial will explain some of the features that are available in the IBM Data Server Gateway for OData Version 1.0.0. IBM Data Server Gateway for OData enables you to quickly create OData RESTful services to query and update data in IBM DB2 LUW.
An introduction to the OData gateway is found in the following developerWorks article:
The code can be obtained through the following link:
In order to help explain some of the features of the OData Gateway, a Jupyter notebook has been created that includes an %odata command that maps DB2 SQL into the equivalent OData syntax. The next command will load the extension and make the command available to this tutorial.
In [1]:
%run db2odata.ipynb
Since we are connecting to a DB2 database, the following command will load the DB2 Jupyter notebook extension (%sql). The DB2 extension allows you to fully interact with the DB2 database, including the ability to drop and create objects. The OData gateway provides INSERT, UPDATE, DELETE, and SELECT capability to the database, but it doesn't have the ability to create or drop actual objects. The other option would be to use DB2 directly on the database server using utilities like CLP (Command Line Processor) or DSM (Data Server Manager).
In [2]:
%run db2.ipynb
Rather than paraphrase what OData does, here is the official statement from the OData home page:
OData (Open Data Protocol) is an ISO/IEC approved, OASIS standard that defines a set of best practices for building and consuming RESTful APIs. OData helps you focus on your business logic while building RESTful APIs without having to worry about the various approaches to define request and response headers, status codes, HTTP methods, URL conventions, media types, payload formats, query options, etc. OData also provides guidance for tracking changes, defining functions/actions for reusable procedures, and sending asynchronous/batch requests.
Customers have a wealth of data in their databases (not just DB2) and publishing it to different devices is often fraught with many challenges. DB2 requires the use of client code to communicate between the application and the database itself. Many of APIs that are used are well known: JDBC, .Net, ODBC, OLE-DB, CLI and so on. Most programming languages have some sort of connector that maps from the language syntax to the database driver. When a new language gets developed it always needs this driver code to talk to the database. For instance, this Python notebook is communicating to DB2 natively using the ibm_db package. Without some specialized coding, there would be no way to communicate with DB2.
OData tries to remove much of the complexity of communicating with the database. There are no drivers required, no configuration file, nor any administration required on the client that is communicating with the database. All communication is done using RESTful API calls, which are available on all browsers and all operating systems. The calls to the database are replaced with standard POST, GET, DELETE, PUT and PATCH requests.
OData goes one step further and removes the syntactical differences between SQL vendors. The INSERT, DELETE, UPDATE and SELECT statements are coverted to a canonical form that should be interpreted by all vendors. Of course, interoperability depends on how much of the standard a vendor has implemented.
The end result is that an Iphone, Andriod phone, tablet, browser or any application will be able to access the database without having any code installed locally. This simplifies the ability to access the database and makes development considerably easier.
The downside to this approach is that the richness of a particular SQL dialect will not be available through OData. Complex SQL with aggregation functions and moving result windows are not a good candidate to use with OData. However, OData covers much of the query spectrum that traditional applications will use, so it makes it a good choice for agile development.
Writing OData calls to DB2 requires a knowledge of the OData syntax, the RESTful calling sequence, and an understanding of the level of support of OData that DB2 provides. This tutorial will take you through all of the functions that the OData gateway currently provides and show how these calls are implemented. Feel free to use the code and extensions in your own applications.
Both the DB2 client and OData calls need connection information. The way that you go about connecting to the database is completely different between these two protocols. Let first start with the DB2 connection.
DB2 requires a userid and password to connect to a database (along with the client code that talks to DB2 over the network). Assuming you have a DB2 database somewhere, the next command will ask you for the following information:
You need to have this information available or the program won't be able to connect. For demonstration purposes, the standard SAMPLE database should be used but in the event you don't have that created, the %sql command will generate the necessary tables for you. It is also good to be a DBADM (database administrator) on the system you are connecting to. This will allow you to create the services requires by the OData gateway. If you don't, someone with that authority will be needed to give you access through OData.
When the next set of commands is issued, the system will prompt you for the information required as well as give you the details for each of the fields.
In [3]:
%sql connect reset
%sql connect to sample
If you connected to the SAMPLE database, you will have the EMPLOYEE and DEPARTMENT tables available to you. However, if you are connecting to a different database, you will need to execute the next command to populate the tables for you. Note, if you run this command and the two tables already exist, the tables will not be replaced. So don't worry if you execute this command by mistake.
In [4]:
%sql -sampledata
Requesting data from DB2 using the standard %sql (ibm_db) interface is relatively straight-forward. We just need to place the SQL in the command and execute it to get the results.
In [5]:
%sql SELECT * FROM EMPLOYEE
Now that we have a working DB2 connection, we will need to set up an OData service to talk to DB2.
Connecting through OData requires a different approach than a DB2 client. We still need to ask a bunch of questions on how we connect to the database, but this doesn't create a connection from the client. Instead what we end up creating is a service URL. This URL gives us access to DB2 through the OData gateway server.
The OData Server take the URL request and maps it to a DB2 resource, which could be one or more tables. The RESTful API needs this URL to communicate with DB2 but nothing else (userids, passwords, etc...) are sent with the request.
The following %odata command will prompt you for the connection parameters, similar to what happened with the DB2 connect. There are a few differences however. The connection requires the userid and password of the user connecting to the database, and the userid and password of a user with administration (DBABM) privileges.
The administrative user creates the service connection that will be used to communicate through the OData gateway and DB2. The regular userid and password is for the actual user that will connect to the database to manipulate the tables. Finally we need to have the schema (or owner) of the tables that will be accessed. From a DB2 perspective, this is similar to connecting to a DATABASE (SAMPLE) as userid FRED. The EMPLOYEE table was created under the userid DB2INST1, so to access the table we need to use DB2INST1.EMPLOYEE. If we didn't include the schema (DB2INST1), the query would fail since FRED was not the owner of the table.
The %odata PROMPT command will request all of the connection parameters and explain what the various fields are. Note: If you have DBADM privileges (and you created the sample tables yourself), you can leave the USERID/PASSWORD/SCHEMA values blank and they will default to the administrative user values.
In [6]:
%odata prompt
So far all we have done is set up the connection parameters, but no actual connection has been made to DB2, nor has an OData service been created. The creation of a service is done when the first SQL request is issued. The next statement will retrieve the values from our favorite EMPLOYEE table, but use OData to accomplish it.
In [7]:
%odata DROP TABLE EMPLOYEE
s = %odata -e SELECT lastname, salary from employee where salary > 50000
Under the covers a number of things happened when running this command. The SELECT * FROM EMPLOYEE is not what is sent to OData. The syntax is converted to something that the RESTful API understands. To view the actual OData syntax, the -e option is used to echo back the commands.
In [8]:
s = %odata -e SELECT * FROM EMPLOYEE
The results will show the URL service command used (http:// followed by details of the host location and service ID) and the OData command. In this case the command should be /EMPLOYEES. This may seem like a spelling mistake, but the OData service creates a mapping from the database table (EMPLOYEE) to a service request. To give the service request a unique name, the letter "S" is appended to the table name. Do not confuse the service name with the table name. That can sometimes lead to coding errors!
If we tried to request a table that didn't exist in the database, we would get an error message instead.
In [9]:
%odata select * from unknown_table
One drawback of OData is that we don't get the actual error text returned. We know that the error code is, but the message isn't that descriptive. Using the %sql (DB2) command, we can find out that the table doesn't exist.
In [10]:
%sql select * from unknown_table
In [11]:
s = %odata -e -j SELECT * FROM EMPLOYEE LIMIT 1
To limit the results from a OData request, you must add the \$top=x modifier at the end of the service request. The format then becomes:
\[service url\]/\[service name\]?$top=valueYou will notice that the OData syntax requires that a "?" be placed after the name of the service. In our example, EMPLOYEES is the name of the service that accesses the EMPLOYEE table. We add the ? after the end of the service name and then add the $top modifier. If there were multiple modifiers, each one must be separated with an ampersand (&) symbol.
What you should have received when running the previous command was a single JSON record, the service URL and the OData command. The URL will be identical to the one in the previous %odata request. There is no need to recreate a service if you are using the same table. The program created a new service when you did a SELECT for the first time. After that it keeps the service information in a file called TABLE@SCHEMA@DATABASE.pickle in the directory where the Jupyter notebook is running. If you try this statement at another time, this service URL will be retrieved from this file rather than creating another service.
If you want to delete the connection information, use the DROP command with the database, schema, and table name in it. This doesn't drop the object or anything associated with the table. All this does is remove the service information from your system. It also does not remove the service from the OData gateway.
In [12]:
%odata \
DROP \
DATABASE {settings['database']} \
SCHEMA {settings['schema']} \
TABLE EMPLOYEE
The last example illustrates two additional features of the %odata command. First, you can span statements over multiple lines by using the backslash character ('\'). You could also use the %%odata command to do this without backslashes, but it unfortunately will not allow for variable substitution. The current settings being used by OData can be found by issuing the SETTINGS command.
You can specify the command with only the TABLE option and it will take the current DATABASE and SCHEMA names from any prior settings.
In [13]:
%odata settings
You can also refer to these values by using the settings['name'] variable. So the DROP statement just took the current DATABASE and SCHEMA settings and deleted the definition for the EMPLOYEE table. You could have done this directly with:
DROP DATABASE SAMPLE SCHEMA DB2INST1 TABLE EMPLOYEEThe list of settings and their variable names are listed below.
| Setting | Variable name |
|---|---|
| DATABASE | settings['database'] |
| SCHEMA | settings['schema'] |
| ADMIN | settings['admin'] |
| A_PWD | settings['a_pwd'] |
| USER | settings['userid'] |
| U_PWD | settings['u_pwd'] |
| HOST | settings['host'] |
| PORT | settings['port'] |
| MAXROWS | settings['maxrows'] |
To use local Jupyter/Python variables in a notebook, all you need to do is place braces {} around the name of the variable. Before we illustrate this, we need to create another connection (since we just dropped it in the last example). Fortunately, none of the settings have been removed, so we still have the connection information (DATABASE, SCHEMA, ...) available.
In the event you have closed the notebook and started up from scratch, there is no need to do a full connect command (or prompt). The settings are automatically written to disk and then restored when you start up another session. If you want to connect to another database then you will need to use the following SET statement.
In [14]:
%odata set DATABASE {settings['database']} SCHEMA {settings['schema']}
And this command will show the connection service being created for us.
In [15]:
u = %odata -e select * from employee limit 1
The %odata command will return the URL command for a select statement as part of the command:
<url> = %odata -e select * from employee limit 1The variable "url" will contain the full URL required to retrieve data from the OData service. The next command illustrates how this works. You must use the echo (-e) option to get the URL returned. Note that you cannot use this syntax with the %%odata version of the command.
In [16]:
url = %odata -e select * from employee limit 1
You can use this URL to directly access the results through a browser, or any application that can read the results returned by the OData gateway. The print statement below will display the URL as an active link. Click on that to see the results in another browser window.
In [17]:
print(url)
When a URL is generated, we need to append the \$format=json tag at the end to tell the OData service and the browser how to handle the results. When we run OData and RESTful calls from a programming language (like Python), we are able to send information in the header which tells the API how to handle the results and parameters. All of the RESTful calls to the OData gateway use the following header information:
{
"Content-Type":"application/json",
"Accept":"application/json"
}
Depending on what version of Firefox you have, you may not get the JSON to be displayed very nicely. To use the built-in JSON formatter, issue the following commands in a separate browser window:
about:config Search for devtools.jsonview.enabled
The %odata command has been designed to translate the SQL syntax for INSERT, DELETE, UPDATE, and SELECT into an equivalent OData format. There are very specific ways of requesting data from OData, so this ends up placing some limitations on what SQL you can use. This section will cover the four major SQL commands and how they can be used with OData. If you need the syntax for a particular SQL command, just enter the command name by itself on the %odata line and it will give you a brief summary of the syntax. Here is the DELETE help.
In [18]:
%odata delete
The SELECT statement is the most complicated of the four statements that are allowed in OData. There are generally two forms that can be used when accessing a record. The first method uses the primary key of the table and it requires no arguments. Note that the examples will not show the URL that points to the OData service.
/EMPLOYEES('000010')
The second method is to use the \$filter query option. \$filter allows us to compare any column against a value. The equivalent OData statement for retrieving an individual employee is:
/EMPLOYEES?$filter=EMPNO eq '000010'The generated SELECT statements will always use this format, rather than relying on a primary key. This becomes more important when we deal with Views.
The SELECT command will return data from one table. There is no ability to join tables with the current implementation of OData. If you do want to join tables, you may want to create a VIEW on the DB2 system and then use that as the TABLE. This will allow for SELECT, but no INSERT/DELETE/UPDATE.
You do not need to use the primary key in the WHERE clause to use this statement. By default, any results will be displayed in a table. If you want to retrieve the results as JSON records, use the -j option on the %odata command.
SELECT \[col1, col2, ... | count(\*)\] FROM <table> \[ WHERE logic\] \[ LIMIT rows \]The column list can contain as many values as you want, or just COUNT(*). COUNT(*) will return the count of rows found. If you use the -r or -j flags to display everything in JSON format, you will also get the entire answer set along with the row count. This is the behavior of using count in OData.
The FROM clause must contain the name of the table you want to access.
The WHERE clause is optional, as is the LIMIT clause. The WHERE clause can contain comparisons between columns and constants (EMPNO='000010'), logic (AND, OR) as well as LIKE clauses (COLUMN LIKE 'xxx'). The current version cannot use arithmetic operators (+, -, *, /) or the NOT operator.
The LIMIT clause will restrict the results to "x" number of rows. So even if there are 500 rows that meet the answer set, only "x" rows will be returned to the client.
In [19]:
s = %odata -e SELECT * FROM EMPLOYEE
You will notice that not all of the rows have been displayed. The output has been limited to 10 lines. 5 lines from the start of the answer set and 5 lines from the bottom of the answer set are displayed. If you want to change the maximum number of rows to be displayed, use the MAXROWS setting.
In [20]:
%odata set maxrows 10
If you want an unlimited number of rows returned, set maxrows to -1.
In [21]:
%odata set maxrows -1
%odata select * from employee
It is better to limit the results from the answer set by using the LIMIT clause in the SELECT statement. LIMIT will force DB2 to stop retrieving rows after "x" number have been read, while the MAXROWS setting will retrieve all rows and then only display a portion of them. The one advantage of MAXROWS is that you see the bottom 5 rows while you would only be able to do that with DB2 if you could reverse sort the output. The current OData implementation does not have the ability to $orderby, so sorting to reverse the output is not possible.
In [22]:
%odata set maxrows 10
In [23]:
s = %odata -e SELECT * FROM EMPLOYEE LIMIT 5
In [24]:
s = %odata -e SELECT FIRSTNME, LASTNAME FROM EMPLOYEE LIMIT 5
The COUNT(*) function is available as part of a SELECT list and it cannot include any other column names. If you do include other column names they will be ignored.
In [25]:
s = %odata -e SELECT COUNT(*) FROM EMPLOYEE
One of the unusual behaviors of the COUNT(*) function is that will actually return the entire answer set under the covers. The %odata command strips the count out from the results and doesn't display the rows returned. That is probably not would you expect from this syntax! The COUNT function is better described as the count of physical rows returned. Here is the same example with 5 rows returned and the JSON records.
In [26]:
s = %odata -e -r SELECT COUNT(*) FROM EMPLOYEE LIMIT 5
One of the recommendations would be not to use the COUNT(*) function to determine the amount of rows that will be retrieved, especially if you expect there to a large of number rows. To minimize the data returned, you can use the form COUNT(column) which will modify the OData request to return the count and ONLY that column in the result set. This is a compromise in terms of the amount of data returned. This example using the -r (raw) flag which results in all of the JSON headers and data to be displayed. The JSON flag (-j) will not display any records.
In [27]:
s = %odata -e -r SELECT COUNT(EMPNO) FROM EMPLOYEE LIMIT 5
The FROM clause is mandatory in any SELECT statement. If an OData service has already been established, there will be no service request sent to OData. Instead, the URL information stored on disk will be used to establish the connection.
If a service has not been established, the %odata command will create the service and then build the OData select statement. If you want to see the command to establish the service as well as the SELECT command, use the -e flag to echo the results.
If the table does not exist in the database you will receive an error message.
In [28]:
%sql -q DROP TABLE UNKNOWN_TBL
%odata DROP TABLE UNKNOWN_TBL
s = %odata -e SELECT * FROM UNKNOWN_TBL
This actually can cause some issues if you try to reuse the connection information that was created with the UNKNOWN_TBL. Since the service could not determine the structure of the table, the service will not return any column information with a select statement. The next SQL statement will create the UNKNOWN_TBL.
In [29]:
%sql CREATE TABLE UNKNOWN_TBL AS (SELECT * FROM EMPLOYEE) WITH DATA
Retrying the SELECT statement will result in 43 rows with no columns returned!
In [30]:
s = %odata -e SELECT * FROM UNKNOWN_TBL
To correct this situation, you need to DROP the connect that the %odata program is using and reissue the SELECT statement.
In [31]:
%odata DROP TABLE UNKNOWN_TBL
Now you can try the SQL statement again.
In [32]:
s = %odata -e SELECT * FROM UNKNOWN_TBL
The SELECT statement needs to know what columns are going to be returned as part of the answer set. The asterix (*) returns all of the columns, but perhaps you only want a few of the columns. To determine what the columns are in the table along with the data types, you can use the DESCRIBE command. The following statement will show the structure of the EMPLOYEE table.
In [33]:
%odata DESCRIBE EMPLOYEE
The datatypes are not the same as what one expect from a relational database. You get generic information on the character columns (String), and the numbers (Int16, Decimal). The Decimal specification actually contains the number of digits and decimal places but that isn't returned when using the table display.
| Data Type | Contents |
|---|---|
| Binary | Binary data |
| Boolean | Binary-valued logic |
| Byte | Unsigned 8-bit integer |
| Date | Date without a time-zone offset |
| Decimal | Numeric values with fixed precision and scale |
| Double | IEEE 754 binary64 floating-point number (15-17 decimal digits) |
| Duration | Signed duration in days, hours, minutes, and (sub)seconds |
| Guid | 16-byte (128-bit) unique identifier |
| Int16 | Signed 16-bit integer |
| Int32 | Signed 32-bit integer |
| Int64 | Signed 64-bit integer |
| SByte | Signed 8-bit integer |
| Single | IEEE 754 binary32 floating-point number (6-9 decimal digits) |
| String | Sequence of UTF-8 characters |
| TimeOfDay | Clock time 00:00-23:59:59.999999999999 |
The WHERE clause is used to filter out the rows that you want to retrieve from the table. The WHERE clause allows the following operators:
The WHERE clause does not allow for mathematical operators at this time (*, -, +, /) or the unary NOT or "-" operators.
The LIKE clause can contain the special % character, but the equivalent OData syntax always searches the entire string and does not anchor at the beginning of the string. What this means is that the LIKE clause will turn into a search of the entire string whether you use the % character in your search string or not.
In [34]:
s = %odata -e SELECT EMPNO, WORKDEPT, SALARY FROM EMPLOYEE WHERE SALARY < 40000
In [35]:
s = %odata -e SELECT EMPNO, WORKDEPT, SALARY FROM EMPLOYEE WHERE SALARY < 40000 AND WORKDEPT = 'E21'
In [36]:
s = %odata -e \
SELECT EMPNO, WORKDEPT, SALARY \
FROM EMPLOYEE \
WHERE SALARY < 40000 AND WORKDEPT = 'E21' OR WORKDEPT = 'E11'
In [37]:
s = %odata -e \
SELECT EMPNO, WORKDEPT, SALARY \
FROM EMPLOYEE \
WHERE SALARY < 40000 AND (WORKDEPT = 'E21' OR WORKDEPT = 'E11')
The LIKE clause in DB2 will look for a string within a character column. Normally the LIKE statement will allow for the use of the % (wildcard) and _ (one character match) operators to look for patterns. These special characters do not exist in OData, so the %odata command will remove the % character and convert it to an equivalent OData statement. What this means is that the string search will look at the entire string for the pattern, while LIKE can be anchored to look only at the beginning of the string. This capability does not current exist with the current OData implementation.
In [38]:
s = %odata -e SELECT LASTNAME FROM EMPLOYEE WHERE LASTNAME LIKE '%AA%'
In [39]:
%sql SELECT LASTNAME FROM EMPLOYEE WHERE LASTNAME LIKE '%ON'
Converting to OData will mean that the search will look across the entire string, not just the beginning.
In [40]:
s = %odata -e SELECT LASTNAME FROM EMPLOYEE WHERE LASTNAME LIKE '%ON'
The LIMIT clause was discussed earlier in this notebook. LIMIT allows you to reduce the amount of rows that are returned in the answer set. The LIMIT clause is similar to FETCH FIRST x ROWS ONLY in DB2. The rows are always taken from the beginning of the answer set so there is no way to skip "x" rows before getting results. The facility does exist in the OData spaecification but has not been implemented in this release.
The LIMIT clause also works in conjunction with the %odata command. The default number of rows that are displayed in a table (result set) is set to 10 by default. So if you have 50 rows in your answer set, the first 5 are displayed and then the last 5 with the rows inbetween are hidden from view. If you want to see the entire answer set, you need to change the MAXROWS value to -1:
%odata SET MAXROWS -1This will display all rows that are returned from the answer set. However, the number of rows actually returned in the anwer set will be determined by the LIMIT clause. If you set LIMIT 5 then only five rows will be returned no matter what MAXROWS is set to. On the other hand, if you set MAXROWS to 10 and LIMIT to 20, you will get 20 rows returned but only 10 will be displayed.
In [41]:
s = %odata -e SELECT * FROM EMPLOYEE LIMIT 5
OData allows you to insert data into a table through the use of the RESTful POST command and a JSON document that contains the field names and contents of those fields.
The format of the INSERT command is:
INSERT INTO <table>(col1, col2, ...) VALUES (val1, val2, ...)The TABLE must be defined before you can issue this statement. There is no requirement to have a primary key on the table, but this will prevent you from updating it with the OData interface because filtering (WHERE) is not allowed on UPDATEs or DELETEs. The column list and value list must match (i.e. there must be a value for every column name). If you do not supply the list of all columns in the table, the missing columns will have null values assigned to them. The insert will fail if any of these missing columns requires a value (NOT NULL).
In [42]:
%%sql -q
DROP TABLE TESTODATA;
CREATE TABLE TESTODATA
(
EMPNO INT NOT NULL,
LASTNAME VARCHAR(10) NOT NULL,
SALARY INT NOT NULL,
BONUS INT
);
We also need to remove the connection information from the system in the event we've run this example before.
In [43]:
%odata DROP TABLE TESTODATA
A couple of things about the table design. The salary is NOT NULL, while the BONUS allows for nulls. Unfortunately, the DESCRIBE command only tells us about the columns in the table and their OData data type, and no indication of whether table.
In [44]:
%odata DESCRIBE TESTODATA
The initial INSERT will populate the table with valid data. The echo option will show the json document that is sent via the POST command to OData to insert the row.
In [45]:
%odata -e INSERT INTO TESTODATA(EMPNO, LASTNAME, SALARY, BONUS) VALUES (1,'Fred',10000,1000)
Just to make sure things were inserted properly, we retrieve the contents of the table.
In [46]:
%odata SELECT * FROM TESTODATA
OData (and DB2) will return an error message about our missing SALARY column which requires a value.
In [47]:
%odata -e INSERT INTO TESTODATA(EMPNO, LASTNAME, BONUS) VALUES (2,'Wilma',50000)
We can try this on the DB2 side as well to get the details of the error.
In [48]:
%sql INSERT INTO TESTODATA(EMPNO, LASTNAME, BONUS) VALUES (2,'Wilma',50000)
The DELETE command only takes one parameter and that is the key value for the record that we want to delete from the table. The format of the command is:
DELETE FROM <table> WHERE KEY=VALUEKey refers to the column that is the primary key in the table we are deleting from. Unless you have a primary key, the DELETE command will not work.
In [49]:
%odata -e DELETE FROM TESTODATA WHERE EMPNO=1
A primary key is required to issue a DELETE command. You also need to make sure that the primary key column does not contain NULLs because a primary key must always contain a value. The following SQL tries to fix the primary key issue.
In [50]:
%sql ALTER TABLE TESTODATA ADD CONSTRAINT PKTD PRIMARY KEY (EMPNO)
Check to see if we can delete the row yet.
In [51]:
%odata -e DELETE FROM TESTODATA WHERE EMPNO=1
Adding a primary key after the fact won't help because the service URL would have already recorded the information about the table (and the fact it didn't have a primary key at the time). We need to drop our SERVICE URL and generate another one.
In [52]:
%odata DROP TABLE TESTODATA
We do a describe on the table and this will force another service URL to be generated for us.
In [53]:
%odata DESCRIBE TESTODATA
Trying the DELETE this time will work.
In [54]:
%odata -e DELETE FROM TESTODATA WHERE EMPNO=1
Deleting the record again still gives you a successful return code. The call always returns a successful status even if the record doesn't exist.
In [55]:
%odata -e DELETE FROM TESTODATA WHERE EMPNO=2
The update command requires both a primary key to update and the name of the field that you want changed. Note that you can only change one field at a time. There is no ability to specify multiple fields at this time.
The format of the UDPATE command is:
UPDATE <table> SET column=value WHERE key=keyvalueYou must have a primary key on the table if you want an update to work. The filtering (WHERE) is allowed only to specify the primary key for the row and no filtering is allowed. The primary can be changed in the statement, but the update will fail if the key already exists in another record. THe other restriction is that no calculations can be done as part of the SET clause. You can only pass atomic values to the UPDATE statement.
In [56]:
%odata -e \
INSERT INTO TESTODATA(EMPNO, LASTNAME, SALARY, BONUS) \
VALUES (1,'Fred',10000,1000)
At this point we can update their salary.
In [57]:
%odata -e UPDATE TESTODATA SET BONUS=2000 WHERE EMPNO=1
We doublecheck the results to make sure we got it right!
In [58]:
%odata SELECT * FROM TESTODATA
The OData implemented with DB2 doesn't allow for JOINS between tables. Sometimes you need to be able to look up information from another table in order to get the final result. One option you have to do this is to create a VIEW on the DB2 system.
The VIEW can contain almost any type of SQL so it allows for very complex queries to be created. For instance, the following view joins the EMPLOYEE table and the DEPARTMENT table to generate a row with the employee name and the name of the department that they work for.
In [59]:
%%sql
CREATE OR REPLACE VIEW EMPDEPT AS
(
SELECT LASTNAME, DEPTNAME
FROM EMPLOYEE E, DEPARTMENT D
WHERE E.WORKDEPT = D.DEPTNO
)
We also need to drop any service connection you may have created in the past with this table name.
In [60]:
%odata DROP TABLE EMPDEPT
Now that we have created the view, we can retrieve rows from it just like a standard table.
In [61]:
%odata SELECT LASTNAME, DEPTNAME FROM EMPDEPT LIMIT 5
You can also create sophisticated VIEWS that can take parameters to adjust the results returned. For instance, consider the following SQL statement which gives me count of employees that work in SYSTEMS departments.
In [62]:
%%sql
SELECT
COUNT(*)
FROM
EMPLOYEE E, DEPARTMENT D
WHERE
E.WORKDEPT = D.DEPTNO
AND D.DEPTNAME LIKE '%SYSTEMS%'
There are two departments with the name SYSTEMS in them, but there is no easy way to create a view for every possible combination of searches that you may want. Instead what we do is create a table that contains the pattern we want to look for and create the view so that it references this table.
The first step is to create our PATTERN table. Note we make sure it has a primary key so our OData update calls can change it!
In [63]:
%%sql -q
DROP TABLE PATTERN;
CREATE TABLE PATTERN
(
PATTERN_NUMBER INT NOT NULL PRIMARY KEY,
SEARCH VARCHAR(16)
);
Now we create a view that access this PATTERN table to do the actual search. Note that values that are inserted into the PATTERN table must have the SQL special characters like % to make sure patterns can be anywhere in the string.
In [64]:
%odata DROP TABLE EMPDEPT
%odata DROP TABLE PATTERN
In [65]:
%%sql
CREATE OR REPLACE VIEW EMPDEPT AS
(
SELECT
COUNT(*) AS COUNT
FROM
EMPLOYEE E, DEPARTMENT D
WHERE
E.WORKDEPT = D.DEPTNO
AND D.DEPTNAME LIKE
(
SELECT SEARCH FROM PATTERN WHERE PATTERN_NUMBER=1
)
);
In order for our view to work properly, we must populate our PATTERN table with a value. To test the view we will use %SYSTEMS% as our first example.
In [66]:
%sql INSERT INTO PATTERN VALUES(1,'%SYSTEMS%')
And now we can test our view by selecting from it.
In [67]:
%sql SELECT * FROM EMPDEPT
Now that we have it working, we can try exactly the same thing but with OData. Our first transaction will update the search key to SERVICE.
In [68]:
%odata UPDATE PATTERN SET SEARCH = '%SERVICE%' WHERE PATTERN_NUMBER = 1
The next OData statement should select the count of employees working in service departments.
In [69]:
%odata SELECT * FROM EMPDEPT
The OData DB2 gateway removes much of the complexity of communicating with the database. There are no drivers required, no configuration file, nor any administration required on the client that is communicating with the database. All communication is done using RESTful API calls, which are available on all browsers and all operating systems. The calls to the database are replaced with standard POST, GET, DELETE, PUT and PATCH requests. Enabling OData support to DB2 opens up the type of applications that you can write and clients that you can connect to DB2 with.